# -*- coding: utf-8 -*-

import base64
import io
import logging
from odoo import models, fields, api, _
from odoo.exceptions import UserError, ValidationError

_logger = logging.getLogger(__name__)

try:
    import openpyxl
except ImportError:
    _logger.warning('openpyxl module not found, BOM import will not work')
    openpyxl = None


class CpqBomImportWizard(models.TransientModel):
    """BOM导入向导 - 支持奔驰和小米两种格式"""
    _name = 'cpq.bom.import.wizard'
    _description = 'BOM导入向导'

    # 项目选择
    project_id = fields.Many2one(
        'cpq.project',
        string='项目',
        required=True
    )
    
    # 文件上传
    file_data = fields.Binary(
        string='Excel文件',
        required=True,
        help='支持奔驰格式(空格缩进)和小米格式(序号层级)'
    )
    file_name = fields.Char(string='文件名')
    
    # 版本信息
    version_type = fields.Selection([
        ('l1', 'L1 - 初版'),
        ('l2', 'L2 - 竞标锁定版'),
        ('l3', 'L3 - SOP版'),
        ('custom', '自定义版本')
    ], string='版本类型', default='l1', required=True)
    
    version_name = fields.Char(
        string='版本名称',
        help='如: V1.0、V2.0'
    )
    
    bom_name = fields.Char(
        string='BOM名称',
        required=True,
        help='如: MS11项目前保总成BOM清单'
    )
    
    # Sheet选择
    sheet_name = fields.Char(
        string='Sheet名称',
        help='要导入的Sheet名称，留空则自动检测'
    )
    
    # 导入选项
    start_row = fields.Integer(
        string='起始行',
        default=1,
        help='数据从第几行开始（表头之后）'
    )
    
    header_row = fields.Integer(
        string='表头行',
        default=4,
        help='列标题所在的行号，用于动态匹配字段'
    )
    
    auto_detect_format = fields.Boolean(
        string='自动检测格式',
        default=True,
        help='自动识别奔驰或小米格式'
    )
    
    force_format = fields.Selection([
        ('benz', '奔驰格式'),
        ('xiaomi', '小米格式')
    ], string='强制格式')
    
    # 导入结果
    import_summary = fields.Html(
        string='导入摘要',
        readonly=True
    )
    
    state = fields.Selection([
        ('upload', '上传文件'),
        ('preview', '预览'),
        ('done', '完成')
    ], default='upload', string='状态')
    
    preview_data = fields.Text(string='预览数据')
    detected_format = fields.Char(string='检测到的格式')
    total_rows = fields.Integer(string='总行数')
    
    def action_preview(self):
        """预览Excel数据"""
        self.ensure_one()
        
        if not openpyxl:
            raise UserError(_('缺少openpyxl模块，请联系管理员安装'))
        
        if not self.file_data:
            raise UserError(_('请上传Excel文件'))
        
        try:
            # 解析Excel
            file_content = base64.b64decode(self.file_data)
            wb = openpyxl.load_workbook(io.BytesIO(file_content), data_only=True)
            
            # 选择Sheet
            if self.sheet_name:
                ws = wb[self.sheet_name]
            else:
                # 自动选择第一个非空Sheet
                ws = wb.active
            
            # 检测格式
            detected_format = self._detect_bom_format(ws)
            
            # 生成预览
            preview_html = self._generate_preview_html(ws, detected_format)
            
            self.write({
                'state': 'preview',
                'detected_format': detected_format,
                'preview_data': preview_html,
                'total_rows': ws.max_row
            })
            
            return {
                'type': 'ir.actions.act_window',
                'res_model': 'cpq.bom.import.wizard',
                'view_mode': 'form',
                'res_id': self.id,
                'target': 'new',
            }
            
        except Exception as e:
            raise UserError(_('预览失败: %s') % str(e))
    
    def action_import(self):
        """执行导入"""
        self.ensure_one()
        
        if not openpyxl:
            raise UserError(_('缺少openpyxl模块，请联系管理员安装'))
        
        if not self.file_data:
            raise UserError(_('请上传Excel文件'))
        
        try:
            # 解析Excel
            file_content = base64.b64decode(self.file_data)
            # 使用data_only=False以支持图片读取
            wb = openpyxl.load_workbook(io.BytesIO(file_content), data_only=False)
            
            # 选择Sheet
            if self.sheet_name:
                ws = wb[self.sheet_name]
            else:
                ws = wb.active
            
            # 提取图片数据
            images_dict = self._extract_images_from_sheet(ws)
            _logger.info(f'从 Excel 中提取到 {len(images_dict)} 张图片')
            
            # 检测或使用强制格式
            if self.auto_detect_format and not self.force_format:
                bom_format = self._detect_bom_format(ws)
            else:
                bom_format = self.force_format or 'unknown'
            
            # 创建BOM版本
            bom_vals = {
                'name': self.bom_name,
                'project_id': self.project_id.id,
                'version_type': self.version_type,
                'version_name': self.version_name,
                'import_file_name': self.file_name,
                'import_format': bom_format,
                'import_date': fields.Datetime.now(),
                'state': 'draft'
            }
            
            bom = self.env['cpq.bom'].create(bom_vals)
            
            # 根据格式解析BOM行
            if bom_format == 'benz':
                lines_data = self._parse_benz_format(ws, bom, images_dict)
            elif bom_format == 'xiaomi':
                lines_data = self._parse_xiaomi_format(ws, bom, images_dict)
            else:
                raise UserError(_('无法识别BOM格式，请手动指定'))
            
            # 批量创建BOM行
            created_lines = self.env['cpq.bom.line'].create(lines_data)
            
            # 建立父子关系（基于level字段）
            self._establish_parent_child_relationship(created_lines)
            
            # 生成导入摘要
            summary = self._generate_import_summary(bom, lines_data, bom_format)
            
            self.write({
                'state': 'done',
                'import_summary': summary
            })
            
            # 返回打开新创建的BOM
            return {
                'type': 'ir.actions.act_window',
                'res_model': 'cpq.bom',
                'view_mode': 'form',
                'res_id': bom.id,
                'target': 'current',
            }
            
        except Exception as e:
            _logger.exception('BOM导入失败')
            raise UserError(_('导入失败: %s') % str(e))
    
    def _extract_images_from_sheet(self, worksheet):
        """从 Excel Sheet 中提取所有图片
        
        Returns:
            dict: 键为 (row, col) 元组，值为 {data: base64_string, filename: str}
        """
        images_dict = {}
        
        try:
            # 获取worksheet中的图片
            if hasattr(worksheet, '_images'):
                for img in worksheet._images:
                    try:
                        # 读取图片数据
                        img_data = img._data()
                        if img_data:
                            # 转换为base64
                            img_base64 = base64.b64encode(img_data).decode('utf-8')
                            
                            # 获取图片位置（行和列）
                            # anchor 属性包含图片的位置信息
                            if hasattr(img, 'anchor'):
                                anchor = img.anchor
                                if hasattr(anchor, '_from'):
                                    # _from 属性包含 col 和 row
                                    col = anchor._from.col + 1  # openpyxl从0开始，我们需要从1开始
                                    row = anchor._from.row + 1
                                    
                                    # 生成文件名
                                    ext = 'png'  # 默认为png
                                    if hasattr(img, 'format'):
                                        ext = img.format or 'png'
                                    filename = f'picture_{row}_{col}.{ext}'
                                    
                                    images_dict[(row, col)] = {
                                        'data': img_base64,
                                        'filename': filename
                                    }
                                    _logger.info(f'提取图片: 行{row}, 列{col}, 文件名: {filename}')
                    except Exception as e:
                        _logger.warning(f'提取图片失败: {e}')
                        continue
        except Exception as e:
            _logger.warning(f'读取Excel图片失败: {e}')
        
        return images_dict
    
    def _detect_bom_format(self, worksheet):
        """
        检测BOM格式
        返回: 'benz' 或 'xiaomi' 或 'unknown'
        """
        # 检查前50行
        for row_idx in range(1, min(51, worksheet.max_row + 1)):
            first_col = worksheet.cell(row_idx, 1).value
            
            # 小米格式特征：序号有小数点 (1.1, 1.2.1)
            if first_col and isinstance(first_col, str):
                if '.' in first_col:
                    parts = first_col.split('.')
                    if len(parts) >= 2 and all(p.strip().isdigit() for p in parts if p.strip()):
                        _logger.info(f'检测到小米格式，特征行: {row_idx}, 值: {first_col}')
                        return 'xiaomi'
            
            # 奔驰格式特征：第一列为空，后续列(D/F)有零件号
            if not first_col or (isinstance(first_col, str) and not first_col.strip()):
                # 检查列D或列F是否有数据
                col_d = worksheet.cell(row_idx, 4).value
                col_f = worksheet.cell(row_idx, 6).value
                if col_d or col_f:
                    _logger.info(f'检测到奔驰格式，特征行: {row_idx}')
                    return 'benz'
        
        return 'unknown'
    
    def _parse_xiaomi_format(self, worksheet, bom, images_dict=None):
        """解析小米格式BOM（序号层级）
        
        Args:
            worksheet: openpyxl worksheet对象
            bom: cpq.bom对象
            images_dict: 图片字典，键为(row, col)，值为base64图片数据
        """
        lines_data = []
        sequence = 10
        images_dict = images_dict or {}
        
        # 跳过表头，从指定行开始
        start_row = self.start_row or 5
        
        _logger.info(f'=== 开始解析小米格式BOM, 起始行: {start_row}, 总行数: {worksheet.max_row} ===')
        
        for row_idx in range(start_row, worksheet.max_row + 1):
            row = list(worksheet.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
            
            # 列A: 序号
            level_number = row[0] if len(row) > 0 else None
            if not level_number:
                continue
            
            # 跳过表头行
            if isinstance(level_number, str) and ('序号' in level_number or 'No.' in level_number):
                _logger.info(f'跳过表头行 {row_idx}: {level_number}')
                continue
            
            # 计算层级
            level = 1
            if isinstance(level_number, str) and '.' in level_number:
                level = len(level_number.split('.'))
            
            # 记录原始数据（用于调试）
            if row_idx <= start_row + 2:  # 只记录前几行
                _logger.info(f'行 {row_idx} 原始数据 (len={len(row)}): {row[:15] if len(row) > 15 else row}')
            
            # 解析字段 - 根据实际Excel列位置
            line_vals = {
                'bom_id': bom.id,
                'sequence': sequence,
                'row_number': row_idx,
                'level_number': str(level_number),
                'level': level,
                'part_number': self._clean_value(row[1]) if len(row) > 1 else None,  # 列B(2): 零件号
                'part_name': self._clean_value(row[2]) if len(row) > 2 else None,    # 列C(3): 零件名称
                # 列D(4): 图片 - 暂时跳过
                'configuration': self._clean_value(row[4]) if len(row) > 4 else None, # 列E(5): 配置及数量
                'assembly_level': self._clean_value(row[5]) if len(row) > 5 else None, # 列F(6): 总成级别
                'part_status': self._parse_part_status(row[6]) if len(row) > 6 else None, # 列G(7): 零件状态
                'process_type': self._parse_process_type(row[7]) if len(row) > 7 else None, # 列H(8): 生产工艺
                'surface_treatment': self._clean_value(row[8]) if len(row) > 8 else None, # 列I(9): 表面处理
                'surface_standard': self._clean_value(row[9]) if len(row) > 9 else None, # 列J(10): 表面处理标准
                'material': self._clean_value(row[10]) if len(row) > 10 else None,     # 列K(11): 材料名称
                'material_standard': self._clean_value(row[11]) if len(row) > 11 else None, # 列L(12): 材料标准
                'dimensions': self._clean_value(row[12]) if len(row) > 12 else None, # 列M(13): 外形尺寸
                'surface_area': self._parse_float(row[13]) if len(row) > 13 else None, # 列N(14): 涂装面积
                'thickness': self._parse_float(row[14]) if len(row) > 14 else None,    # 列O(15): 壁厚
                'weight': self._parse_float_kg_to_g(row[15]) if len(row) > 15 else None, # 列P(16): 重量(kg->g)
                'notes': self._clean_value(row[16]) if len(row) > 16 else None,      # 列Q(17): 备注
                'make_or_buy': self._parse_make_or_buy(row[18]) if len(row) > 18 else None, # 列S(19): P/L
            }
            
            # 添加图片（列D，第4列）
            picture_key = (row_idx, 4)  # 列D的索引是4
            if picture_key in images_dict:
                line_vals['picture'] = images_dict[picture_key]['data']
                line_vals['picture_filename'] = images_dict[picture_key]['filename']
            
            # 只有零件名称的才是有效数据
            if line_vals['part_name']:
                # 记录第一条数据的详细信息
                if len(lines_data) == 0:
                    _logger.info(f'第一条有效数据 (line_vals): {line_vals}')
                lines_data.append(line_vals)
                sequence += 10
        
        _logger.info(f'=== 小米格式解析完成, 共解析 {len(lines_data)} 条数据 ===')
        return lines_data
    
    def _parse_benz_format(self, worksheet, bom, images_dict=None):
        """解析奔驰格式BOM（基于缩进的动态层级识别 + 基于表头的字段匹配）
        
        核心逻辑：
        1. 找到每行第一个看起来像零件号的列（以字母开头+包含数字）
        2. 根据零件号列的位置动态判断层级关系
        3. 读取表头行，根据表头关键字匹配字段
        
        Args:
            worksheet: openpyxl worksheet对象
            bom: cpq.bom对象
            images_dict: 图片字典，键为(row, col)，值为base64图片数据
        """
        lines_data = []
        sequence = 10
        images_dict = images_dict or {}
        
        start_row = self.start_row or 5
        header_row = self.header_row or 4
        
        # 读取表头行，构建字段映射
        _logger.info(f'读取第{header_row}行作为表头...')
        header = list(worksheet.iter_rows(min_row=header_row, max_row=header_row, values_only=True))[0]
        _logger.info(f'表头内容: {header[:20]}')
        
        # 字段映射：根据表头关键字匹配列索引
        field_mapping = self._build_field_mapping(header)
        _logger.info(f'字段映射: {field_mapping}')
        
        # 查找包含“图”和“片”的列（图片列）
        # 对于奔驰BOM，表头在第4行，数据从第6行开始
        picture_col = None
        # 尝试多个可能的表头行
        for header_row in [4, start_row - 1, 3, 2, 1]:
            if header_row > 0:
                try:
                    header = list(worksheet.iter_rows(min_row=header_row, max_row=header_row, values_only=True))[0]
                    _logger.info(f'检查第{header_row}行作为表头: {header[:10]}')
                    
                    for col_idx, cell_value in enumerate(header, start=1):
                        if cell_value and isinstance(cell_value, str):
                            # 查找包含“图”和“片”的列
                            if '图' in cell_value and '片' in cell_value:
                                picture_col = col_idx
                                _logger.info(f'✅ 找到图片列: 第{header_row}行, 列{col_idx} - "{cell_value}"')
                                break
                    
                    if picture_col:
                        break  # 找到后停止搜索
                except Exception as e:
                    _logger.warning(f'检查第{header_row}行失败: {e}')
        
        if not picture_col:
            _logger.warning('⚠️  未找到包含“图”和“片”的列')
        
        _logger.info(f'=== 开始解析奔驰格式BOM (动态层级识别), 起始行: {start_row}, 总行数: {worksheet.max_row}, 图片列: {picture_col} ===')
        
        # 层级编号计数器
        level_counters = {}
        level_paths = {}
        # 记录每个列位置对应的层级 {col_index: level}
        col_to_level = {}
        # 记录已知的所有零件号列位置（排序后）
        known_cols = []
        
        for row_idx in range(start_row, worksheet.max_row + 1):
            row = list(worksheet.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
            
            # 找零件号：从左到右扫描，找到第一个看起来像零件号的列
            # 零件号通常是以字母开头并包含数字的编码（如A1678801311）
            part_number_col = None
            part_number = None
            part_name = None
            
            for col_idx in range(min(20, len(row))):
                val = row[col_idx]
                if val and str(val).strip():
                    val_str = str(val).strip()
                    # 跳过纯数字的序号列
                    if val_str.isdigit():
                        continue
                    # 跳过表头关键字
                    if any(keyword in val_str for keyword in ['序号', 'No.', '零件', '名称', 'Part']):
                        _logger.info(f'跳过表头行 {row_idx}: {val_str}')
                        break
                    # 找到疑似零件号的列（以字母开头，包含数字）
                    if val_str[0].isalpha() and any(c.isdigit() for c in val_str):
                        part_number_col = col_idx
                        part_number = val_str
                        # 零件名称通常在零件号的下一列
                        if col_idx + 1 < len(row) and row[col_idx + 1]:
                            part_name = str(row[col_idx + 1]).strip()
                        break
            
            # 如果没找到零件号，跳过这行
            if part_number_col is None or not part_name:
                continue
            
            # 记录原始数据（前10行）
            if row_idx <= start_row + 10:
                _logger.info(f'行{row_idx} 零件号列={part_number_col}(0-based), 零件号={part_number}, 名称={part_name[:30] if len(part_name) > 30 else part_name}')
            
            # 动态判断层级：根据零件号列的位置
            if not col_to_level:
                # 第一个零件，记录为一级
                level = 1
                col_to_level[part_number_col] = 1
                known_cols = [part_number_col]
                _logger.info(f'发现第一个零件（一级），列位置={part_number_col}(0-based)')
            else:
                # 检查当前列是否是已知层级
                if part_number_col in col_to_level:
                    # 该列已知，直接使用已知层级
                    level = col_to_level[part_number_col]
                else:
                    # 新的列位置，判断层级关系
                    # 找到所有列位置小于当前列的已知列（潜在父级）
                    parent_cols = [col for col in known_cols if col < part_number_col]
                    if parent_cols:
                        # 这是子级：找到最近的父级列（列位置小于当前列的最大列）
                        parent_col = max(parent_cols)
                        parent_level = col_to_level[parent_col]
                        level = parent_level + 1
                        col_to_level[part_number_col] = level
                        known_cols.append(part_number_col)
                        known_cols.sort()
                        if row_idx <= start_row + 10:
                            _logger.info(f'发现新子级：列{part_number_col}(0-based) 是 列{parent_col}(0-based) 的子级，层级={level}')
                    else:
                        # 列位置比所有已知列都靠左，这是新的顶层
                        min_level = min(col_to_level.values())
                        level = min_level
                        col_to_level[part_number_col] = level
                        known_cols.append(part_number_col)
                        known_cols.sort()
            
            # 一级零件的LU字段
            lu_part_number = None
            lu_part_name = None
            if level == 1:
                lu_part_number = part_number
                lu_part_name = part_name
            
            # 生成层级编号（如 1, 1.1, 1.1.1, 2, 2.1）
            if level == 1:
                # 一级：重置计数器
                level_counters[1] = level_counters.get(1, 0) + 1
                # 清除子级计数器
                for l in range(2, 10):
                    level_counters[l] = 0
                level_number = str(level_counters[1])
                level_paths[1] = [level_counters[1]]
            else:
                # 子级：继承父级路径 + 自己的计数
                level_counters[level] = level_counters.get(level, 0) + 1
                # 清除更深层级的计数器
                for l in range(level + 1, 10):
                    level_counters[l] = 0
                
                # 构建路径：父级路径 + 当前计数
                parent_level = level - 1
                if parent_level in level_paths:
                    parent_path = level_paths[parent_level].copy()
                    parent_path.append(level_counters[level])
                    level_paths[level] = parent_path
                    level_number = '.'.join(map(str, parent_path))
                else:
                    # 如果找不到父级路径，使用简单编号
                    level_number = str(level_counters[level])
                    level_paths[level] = [level_counters[level]]
            
            # 记录调试信息
            if row_idx <= start_row + 10:
                _logger.info(f'行{row_idx}: 列{part_number_col}(0-based) → 层级{level}, 编号: {level_number}')
            
            # 根据字段映射解析其他字段（动态匹配）
            line_vals = {
                'bom_id': bom.id,
                'sequence': sequence,
                'row_number': row_idx,
                'level': level,
                'level_number': level_number,  # 生成的层级编号
                'part_number': self._clean_value(part_number),
                'part_name': self._clean_value(part_name),
                'lu_part_number': self._clean_value(lu_part_number),
                'lu_part_name': self._clean_value(lu_part_name),
            }
            
            # 根据字段映射动态添加其他字段
            if 'configuration' in field_mapping:
                col_idx = field_mapping['configuration']
                line_vals['configuration'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'assembly_level' in field_mapping:
                col_idx = field_mapping['assembly_level']
                line_vals['assembly_level'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'part_status' in field_mapping:
                col_idx = field_mapping['part_status']
                line_vals['part_status'] = self._parse_part_status(row[col_idx]) if len(row) > col_idx else None
            
            if 'process_type' in field_mapping:
                col_idx = field_mapping['process_type']
                line_vals['process_type'] = self._parse_process_type(row[col_idx]) if len(row) > col_idx else None
            
            if 'surface_treatment' in field_mapping:
                col_idx = field_mapping['surface_treatment']
                line_vals['surface_treatment'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'surface_standard' in field_mapping:
                col_idx = field_mapping['surface_standard']
                line_vals['surface_standard'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'material' in field_mapping:
                col_idx = field_mapping['material']
                line_vals['material'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'material_standard' in field_mapping:
                col_idx = field_mapping['material_standard']
                line_vals['material_standard'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'dimensions' in field_mapping:
                col_idx = field_mapping['dimensions']
                line_vals['dimensions'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'surface_area' in field_mapping:
                col_idx = field_mapping['surface_area']
                line_vals['surface_area'] = self._parse_float(row[col_idx]) if len(row) > col_idx else None
            
            if 'thickness' in field_mapping:
                col_idx = field_mapping['thickness']
                line_vals['thickness'] = self._parse_float(row[col_idx]) if len(row) > col_idx else None
            
            if 'weight' in field_mapping:
                col_idx = field_mapping['weight']
                line_vals['weight'] = self._parse_float_kg_to_g(row[col_idx]) if len(row) > col_idx else None
            
            if 'notes' in field_mapping:
                col_idx = field_mapping['notes']
                line_vals['notes'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            if 'make_or_buy' in field_mapping:
                col_idx = field_mapping['make_or_buy']
                line_vals['make_or_buy'] = self._parse_make_or_buy(row[col_idx]) if len(row) > col_idx else None
            
            if 'code' in field_mapping:
                col_idx = field_mapping['code']
                line_vals['code'] = self._clean_value(row[col_idx]) if len(row) > col_idx else None
            
            # 添加图片（使用识别到的图片列）
            if picture_col:
                picture_key = (row_idx, picture_col)
                if picture_key in images_dict:
                    line_vals['picture'] = images_dict[picture_key]['data']
                    line_vals['picture_filename'] = images_dict[picture_key]['filename']
                    _logger.info(f'行 {row_idx} 找到图片于列 {picture_col}')
            
            # 记录第一条数据的详细信息
            if len(lines_data) == 0:
                _logger.info(f'第一条有效数据 (line_vals): {line_vals}')
            
            lines_data.append(line_vals)
            sequence += 10
        
        _logger.info(f'=== 奔驰格式解析完成, 共解析 {len(lines_data)} 条数据 ===')
        _logger.info(f'发现的层级-列位置映射: {col_to_level}')
        return lines_data
    
    def _parse_part_status(self, value):
        """解析零件状态"""
        if not value:
            return None
        
        value_str = str(value).upper()
        if 'NEW' in value_str:
            return 'new'
        elif 'CARRYOVER' in value_str:
            return 'carryover'
        elif 'MODIFIED' in value_str:
            return 'modified'
        
        return None
    
    def _parse_make_or_buy(self, value):
        """解析L/P（自制/外采）"""
        if not value:
            return None
        value_str = str(value).upper().strip()
        if value_str == 'L':
            return 'L'
        elif value_str == 'P':
            return 'P'
        return None
    
    def _parse_float(self, value):
        """解析浮点数"""
        if value is None:
            return None
        try:
            return float(value)
        except (ValueError, TypeError):
            return None
    
    def _parse_float_kg_to_g(self, value):
        """解析重量（从kg转换为g）"""
        if value is None:
            return None
        try:
            kg_value = float(value)
            return kg_value * 1000  # kg -> g
        except (ValueError, TypeError):
            return None
    
    def _parse_process_type(self, value):
        """解析生产工艺"""
        if not value:
            return None
        value_str = str(value).lower()
        # 映射到selection值
        if '注塑' in value_str and '喷' in value_str:
            return 'injection_painting'
        elif '注塑' in value_str:
            return 'injection'
        elif '喷' in value_str or '涂装' in value_str:
            return 'painting'
        elif '装配' in value_str or 'ass' in value_str:
            return 'assembly'
        elif '金属' in value_str:
            return 'metal'
        elif '发泡' in value_str:
            return 'foam'
        else:
            return 'other'
    
    def _clean_value(self, value):
        """清理字符串值"""
        if value is None:
            return None
        value_str = str(value).strip()
        # 如果是XX或空，返回None
        if value_str in ('XX', 'xx', ''):
            return None
        return value_str
    
    def _generate_preview_html(self, worksheet, detected_format):
        """生成预览HTML"""
        html = f'<div style="font-family: monospace;">'
        html += f'<p><strong>检测到的格式:</strong> {detected_format}</p>'
        html += f'<p><strong>总行数:</strong> {worksheet.max_row}</p>'
        html += '<table border="1" cellpadding="5" style="border-collapse: collapse;">'
        
        # 显示前20行
        for row_idx in range(1, min(21, worksheet.max_row + 1)):
            html += '<tr>'
            row = list(worksheet.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
            for col_idx in range(min(8, len(row))):
                val = row[col_idx] or ''
                html += f'<td>{val}</td>'
            html += '</tr>'
        
        html += '</table></div>'
        return html
    
    def _generate_import_summary(self, bom, lines_data, bom_format):
        """生成导入摘要"""
        format_name = {
            'benz': '奔驰格式(空格缩进)',
            'xiaomi': '小米格式(序号层级)',
            'unknown': '未知格式'
        }.get(bom_format, bom_format)
        
        summary = f"""
        <div style="padding: 20px;">
            <h3>✅ BOM导入成功！</h3>
            <table style="width: 100%; margin-top: 20px;">
                <tr>
                    <td><strong>BOM名称:</strong></td>
                    <td>{bom.name}</td>
                </tr>
                <tr>
                    <td><strong>项目:</strong></td>
                    <td>{bom.project_id.name}</td>
                </tr>
                <tr>
                    <td><strong>版本类型:</strong></td>
                    <td>{dict(bom._fields['version_type'].selection).get(bom.version_type)}</td>
                </tr>
                <tr>
                    <td><strong>检测格式:</strong></td>
                    <td>{format_name}</td>
                </tr>
                <tr>
                    <td><strong>导入行数:</strong></td>
                    <td>{len(lines_data)} 行</td>
                </tr>
                <tr>
                    <td><strong>导入时间:</strong></td>
                    <td>{fields.Datetime.now()}</td>
                </tr>
            </table>
        </div>
        """
        return summary
    
    def _build_field_mapping(self, header):
        """根据表头行构建字段映射
        
        Args:
            header: 表头行数据（tuple）
            
        Returns:
            dict: {字段名: 列索引}
        """
        mapping = {}
        
        # 定义表头关键字到字段的映射规则
        # 每个字段对应一个关键字列表，匹配时忽略大小写
        keyword_rules = {
            'configuration': ['配置', '数量', 'config', 'quantity'],
            'assembly_level': ['总成', '级别', 'assembly', 'level'],
            'part_status': ['状态', 'status', 'new', 'carryover'],
            'process_type': ['工艺', '制造', 'process', '注塑', '喷涂', 'injection', 'painting'],
            'surface_treatment': ['表面处理', '表处', 'surface', 'treatment'],
            'surface_standard': ['表面标准', '表准'],
            'material': ['材料', 'material', 'mat'],
            'material_standard': ['材料标准', '材准'],
            'dimensions': ['尺寸', '外形', 'dimension', 'size'],
            'surface_area': ['面积', '涂装', 'area', 'surface'],
            'thickness': ['壁厚', '厚度', 'thickness', 'thick'],
            'weight': ['克重', '重量', 'weight', 'kg', 'g'],
            'notes': ['备注', '说明', 'note', 'remark', 'comment'],
            'make_or_buy': ['自制', '外采', 'L/P', 'L', 'P'],
            'code': ['CODE', '代码', 'code'],
        }
        
        # 遍历表头，匹配关键字
        for col_idx, cell_value in enumerate(header):
            if not cell_value:
                continue
            
            cell_str = str(cell_value).strip()
            if not cell_str:
                continue
            
            # 尝试匹配每个字段
            for field_name, keywords in keyword_rules.items():
                # 如果该字段已经匹配过，跳过
                if field_name in mapping:
                    continue
                
                # 检查是否包含任何关键字
                for keyword in keywords:
                    if keyword.lower() in cell_str.lower() or keyword in cell_str:
                        mapping[field_name] = col_idx
                        _logger.info(f'匹配字段: {field_name} -> 列{col_idx} ("{cell_str}")')
                        break
        
        return mapping
    
    def _establish_parent_child_relationship(self, lines):
        """建立父子关系（基于level字段）
        
        Args:
            lines: cpq.bom.line recordset
        """
        if not lines:
            return
        
        # 按sequence排序（保持导入顺序）
        lines = lines.sorted('sequence')
        
        # 记录每个层级的最后一个节点（用于找父级）
        level_last_node = {}  # {level: line_id}
        
        for line in lines:
            level = line.level or 1
            
            if level == 1:
                # 一级零件，没有父级
                line.parent_id = False
                level_last_node[1] = line.id
            else:
                # 子级零件，找到父级（level - 1层的最后一个节点）
                parent_level = level - 1
                if parent_level in level_last_node:
                    line.parent_id = level_last_node[parent_level]
                else:
                    # 如果找不到父级，设置为False
                    line.parent_id = False
            
            # 更新当前层级的最后一个节点
            level_last_node[level] = line.id
            
            # 清除更深层级的记录（因为当前节点已经不是它们的父级）
            for l in list(level_last_node.keys()):
                if l > level:
                    del level_last_node[l]
        
        _logger.info(f'已为 {len(lines)} 条BOM行建立父子关系')
